﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class Secured_AddIncome : System.Web.UI.Page
{
    SqlConnection conn;
    SqlCommand cmd;
    SqlDataReader dr;
    bool IsExisting;
    double Amt = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        conn = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["connstr"].ConnectionString);
        if (!Page.IsPostBack)
        {
            if (conn.State != System.Data.ConnectionState.Open)
            {
                try
                {
                    conn.Open();
                    FillDate();
                    FillExpenseHeads();
                    FillMembers();
                    FillExpenseYear(DateTime.Now.Year);
                }
                catch (SqlException ex)
                {
                    ltMsg.Text = ex.Message;
                }
            }
        }
    }
    private void FillExpenseYear(int CurrentYear)
    {
        cmbExpenseYear.Items.Clear();
        while (CurrentYear <= 2020)
        {
            cmbExpenseYear.Items.Add(CurrentYear.ToString());
            CurrentYear += 1;
        }
    }
    private void FillMembers()
    {
        cmbExpensePaidBy.Items.Clear();
        cmbExpensePaidBy.Items.Add(string.Empty);
        cmd = new SqlCommand("Select UserName from HAT_UserAccounts where IsApproved=@IsApproved order by UserName asc", conn);
        cmd.Parameters.Add("@IsApproved", System.Data.SqlDbType.Bit).Value = true;
        try
        {
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                cmbExpensePaidBy.Items.Add(dr.GetString(0).ToString());
            }
            dr.Close();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
    }
    private void FillDate()
    {
        txtIncomeDate.Text = DateTime.Today.ToShortDateString();
        txtIncomeDate.Focus();
    }
    private void FillExpenseHeads()
    {
        cmbExpenseHead.Items.Clear();
        cmbExpenseHead.Items.Add(string.Empty);
        cmd = new SqlCommand("Select ExpenseHead from HAT_ExpenseHead order by ExpenseHead asc", conn);
        try
        {
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                cmbExpenseHead.Items.Add(dr.GetString(0).ToString());
            }
            dr.Close();
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
    }
    protected void btnAddIncome_Command(object sender, CommandEventArgs e)
    {
        if (e.CommandName.Equals("AddIncome"))
        {
            if ((cmbExpenseHead.SelectedValue != string.Empty) & (cmbExpensePaidBy.SelectedValue != string.Empty))
            {
                CheckForExistingItem(cmbExpensePaidBy.SelectedValue, cmbExpenseHead.SelectedValue, "0" + (cmbExpenseMonth.SelectedIndex + 1) + "/" + cmbExpenseYear.SelectedValue);
                if (IsExisting)
                    UpdateRecords(cmbExpensePaidBy.SelectedValue, cmbExpenseHead.SelectedValue, "0" + (cmbExpenseMonth.SelectedIndex + 1) + "/" + cmbExpenseYear.SelectedValue, Convert.ToDouble(txtIncomeAmount.Text));
                else
                    AddFreshIncome();
            }
        }
        else
        {
            ltMsg.Text = "Mandatory Fields Missing";
        }
    }
    private void UpdateRecords(string UserID, string ExpenseHead, string ForPeriod, double IncomeAmount)
    {
        Type cstype = this.GetType();
        Amt += IncomeAmount;
        cmd = new SqlCommand("Update HAT_Income set IncomeAmount=@Amt, Comments=@LastUpdated where PaidBy=@PaidBy and ForPeriod=@For and ExpenseHead=@ExpHead ", conn);
        cmd.Parameters.Add("@PaidBy", System.Data.SqlDbType.VarChar).Value = UserID;
        cmd.Parameters.Add("@ExpHead", System.Data.SqlDbType.VarChar).Value = ExpenseHead;
        cmd.Parameters.Add("@For", System.Data.SqlDbType.VarChar).Value = ForPeriod;
        cmd.Parameters.Add("@Amt", System.Data.SqlDbType.Decimal).Value = Amt;
        cmd.Parameters.Add("@LastUpdated", System.Data.SqlDbType.VarChar).Value = "Last Updated:" + DateTime.Now;
        try
        {
            cmd.ExecuteNonQuery();
            Page.ClientScript.RegisterClientScriptBlock(cstype, "ExpenseAdded", "alert('Expense Added Successfully')", true);
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
    }
    private void AddFreshIncome()
    {
        Type cstype = this.GetType();
        cmd = new SqlCommand("Insert into HAT_Income(IncomeDate,ExpenseHead,PaidBy,ForPeriod,IncNotes,IncomeAmount) values(@IncDate,@ExHead,@PaidBy,@For,@Notes,@Amt)", conn);
        cmd.Parameters.Add("@IncDate", System.Data.SqlDbType.DateTime).Value = txtIncomeDate.Text;
        cmd.Parameters.Add("@ExHead", System.Data.SqlDbType.VarChar).Value = cmbExpenseHead.SelectedValue;
        cmd.Parameters.Add("@PaidBy", System.Data.SqlDbType.VarChar).Value = cmbExpensePaidBy.SelectedValue;
        cmd.Parameters.Add("@For", System.Data.SqlDbType.VarChar).Value = "0" + (cmbExpenseMonth.SelectedIndex + 1) + "/" + cmbExpenseYear.SelectedValue;
        cmd.Parameters.Add("@Notes", System.Data.SqlDbType.VarChar).Value = txtIncNotes.Text.Trim();
        cmd.Parameters.Add("@Amt", System.Data.SqlDbType.Decimal).Value = txtIncomeAmount.Text;
        try
        {
            cmd.ExecuteNonQuery();
            Page.ClientScript.RegisterClientScriptBlock(cstype, "ExpenseAdded", "alert('Expense Added Successfully')", true);
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
    }
    private void CheckForExistingItem(string UserID, string ExpenseHead, string ForPeriod)
    {
        cmd = new SqlCommand("Select IncomeAmount from HAT_Income where PaidBy=@PaidBy and ForPeriod=@ForPeriod and ExpenseHead=@ExHead", conn);
        cmd.Parameters.Add("@PaidBy", System.Data.SqlDbType.VarChar).Value = UserID;
        cmd.Parameters.Add("@ForPeriod", System.Data.SqlDbType.VarChar).Value = ForPeriod;
        cmd.Parameters.Add("@ExHead", System.Data.SqlDbType.VarChar).Value = ExpenseHead;
        try
        {
            conn.Open();
            dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    Amt = Convert.ToDouble(dr.GetDecimal(0));
                }
                dr.Close();
                IsExisting = true;
            }
            else
            {
                IsExisting = false;
                dr.Close();
            }
        }
        catch (SqlException ex)
        {
            ltMsg.Text = ex.Message;
        }
    }
}